<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<title>Create Or Redefine SQL Functions</title>
<style type="text/css">
body {
    margin: auto;
    font-family: Verdana, sans-serif;
    padding: 8px 1%;
}

a { color: #044a64 }
a:visited { color: #734559 }

.logo { position:absolute; margin:3px; }
.tagline {
  float:right;
  text-align:right;
  font-style:italic;
  width:300px;
  margin:12px;
  margin-top:58px;
}

.toolbar {
  text-align: center;
  line-height: 1.6em;
  margin: 0;
  padding: 0px 8px;
}
.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
.toolbar a:visited { color: white; }
.toolbar a:hover { color: #044a64; background: white; }

.content    { margin: 5%; }
.content dt { font-weight:bold; }
.content dd { margin-bottom: 25px; margin-left:20%; }
.content ul { padding:0px; padding-left: 15px; margin:0px; }

/* rounded corners */
.se  { background: url(../images/se.gif) 100% 100% no-repeat #044a64}
.sw  { background: url(../images/sw.gif) 0% 100% no-repeat }
.ne  { background: url(../images/ne.gif) 100% 0% no-repeat }
.nw  { background: url(../images/nw.gif) 0% 0% no-repeat }

/* Things for "fancyformat" documents start here. */
.fancy img+p {font-style:italic}
.fancy .codeblock i { color: darkblue; }
.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
.fancy h2 { margin-left: 10px }
.fancy h3 { margin-left: 20px }
.fancy h4 { margin-left: 30px }
.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
.fancy #toc a        { color: darkblue ; text-decoration: none }
.fancy .todo         { color: #AA3333 ; font-style : italic }
.fancy .todo:before  { content: 'TODO:' }
.fancy p.todo        { border: solid #AA3333 1px; padding: 1ex }
.fancy img { display:block; }
.fancy :link:hover, .fancy :visited:hover { background: wheat }
.fancy p,.fancy ul,.fancy ol { margin: 1em 5ex }
.fancy li p { margin: 1em 0 }
/* End of "fancyformat" specific rules. */

</style>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
</head>
<body>
<div><!-- container div to satisfy validator -->

<a href="../index.html">
<img class="logo" src="../images/sqlite370_banner.gif" alt="SQLite Logo"
 border="0"></a>
<div><!-- IE hack to prevent disappearing logo--></div>
<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>

<table width=100% style="clear:both"><tr><td>
  <div class="se"><div class="sw"><div class="ne"><div class="nw">
  <table width=100% style="padding:0;margin:0;cell-spacing:0"><tr>
  <td width=100%>
  <div class="toolbar">
    <a href="../about.html">About</a>
    <a href="../sitemap.html">Sitemap</a>
    <a href="../docs.html">Documentation</a>
    <a href="../download.html">Download</a>
    <a href="../copyright.html">License</a>
    <a href="../news.html">News</a>
    <a href="../support.html">Support</a>
  </div>
<script>
  gMsg = "Search SQLite Docs..."
  function entersearch() {
    var q = document.getElementById("q");
    if( q.value == gMsg ) { q.value = "" }
    q.style.color = "black"
    q.style.fontStyle = "normal"
  }
  function leavesearch() {
    var q = document.getElementById("q");
    if( q.value == "" ) { 
      q.value = gMsg
      q.style.color = "#044a64"
      q.style.fontStyle = "italic"
    }
  }
</script>
<td>
    <div style="padding:0 1em 0px 0;white-space:nowrap">
    <form name=f method="GET" action="http://www.sqlite.org/search">
      <input id=q name=q type=text
       onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
      <input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
    </form>
    </div>
  </table>
</div></div></div></div>
</td></tr></table>
<div class=startsearch></div>
  
<a href="intro.html"><h2>SQLite C Interface</h2></a><h2>Create Or Redefine SQL Functions</h2><blockquote><pre>int sqlite3_create_function(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
int sqlite3_create_function16(
  sqlite3 *db,
  const void *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*)
);
int sqlite3_create_function_v2(
  sqlite3 *db,
  const char *zFunctionName,
  int nArg,
  int eTextRep,
  void *pApp,
  void (*xFunc)(sqlite3_context*,int,sqlite3_value**),
  void (*xStep)(sqlite3_context*,int,sqlite3_value**),
  void (*xFinal)(sqlite3_context*),
  void(*xDestroy)(void*)
);
</pre></blockquote><p>
These functions (collectively known as "function creation routines")
are used to add SQL functions or aggregates or to redefine the behavior
of existing SQL functions or aggregates.  The only differences between
these routines are the text encoding expected for
the second parameter (the name of the function being created)
and the presence or absence of a destructor callback for
the application data pointer.</p>

<p>The first parameter is the <a href="../c3ref/sqlite3.html">database connection</a> to which the SQL
function is to be added.  If an application uses more than one database
connection then application-defined SQL functions must be added
to each database connection separately.</p>

<p>The second parameter is the name of the SQL function to be created or
redefined.  The length of the name is limited to 255 bytes in a UTF-8
representation, exclusive of the zero-terminator.  Note that the name
length limit is in UTF-8 bytes, not characters nor UTF-16 bytes.
Any attempt to create a function with a longer name
will result in <a href="../c3ref/c_abort.html">SQLITE_MISUSE</a> being returned.</p>

<p>The third parameter (nArg)
is the number of arguments that the SQL function or
aggregate takes. If this parameter is -1, then the SQL function or
aggregate may take any number of arguments between 0 and the limit
set by <a href="../c3ref/limit.html">sqlite3_limit</a>(<a href="../c3ref/c_limit_attached.html">SQLITE_LIMIT_FUNCTION_ARG</a>).  If the third
parameter is less than -1 or greater than 127 then the behavior is
undefined.</p>

<p>The fourth parameter, eTextRep, specifies what
<a href="../c3ref/c_any.html">text encoding</a> this SQL function prefers for
its parameters.  Every SQL function implementation must be able to work
with UTF-8, UTF-16le, or UTF-16be.  But some implementations may be
more efficient with one encoding than another.  An application may
invoke sqlite3_create_function() or sqlite3_create_function16() multiple
times with the same function but with different values of eTextRep.
When multiple implementations of the same function are available, SQLite
will pick the one that involves the least amount of data conversion.
If there is only a single implementation which does not care what text
encoding is used, then the fourth argument should be <a href="../c3ref/c_any.html">SQLITE_ANY</a>.</p>

<p>The fifth parameter is an arbitrary pointer.  The implementation of the
function can gain access to this pointer using <a href="../c3ref/user_data.html">sqlite3_user_data()</a>.</p>

<p>The sixth, seventh and eighth parameters, xFunc, xStep and xFinal, are
pointers to C-language functions that implement the SQL function or
aggregate. A scalar SQL function requires an implementation of the xFunc
callback only; NULL pointers must be passed as the xStep and xFinal
parameters. An aggregate SQL function requires an implementation of xStep
and xFinal and NULL pointer must be passed for xFunc. To delete an existing
SQL function or aggregate, pass NULL pointers for all three function
callbacks.</p>

<p>If the ninth parameter to sqlite3_create_function_v2() is not NULL,
then it is destructor for the application data pointer.
The destructor is invoked when the function is deleted, either by being
overloaded or when the database connection closes.
The destructor is also invoked if the call to
sqlite3_create_function_v2() fails.
When the destructor callback of the tenth parameter is invoked, it
is passed a single argument which is a copy of the application data
pointer which was the fifth parameter to sqlite3_create_function_v2().</p>

<p>It is permitted to register multiple implementations of the same
functions with the same name but with either differing numbers of
arguments or differing preferred text encodings.  SQLite will use
the implementation that most closely matches the way in which the
SQL function is used.  A function implementation with a non-negative
nArg parameter is a better match than a function implementation with
a negative nArg.  A function where the preferred text encoding
matches the database encoding is a better
match than a function where the encoding is different.
A function where the encoding difference is between UTF16le and UTF16be
is a closer match than a function where the encoding difference is
between UTF8 and UTF16.</p>

<p>Built-in functions may be overloaded by new application-defined functions.</p>

<p>An application-defined function is permitted to call other
SQLite interfaces.  However, such calls must not
close the database connection nor finalize or reset the prepared
statement in which the function is running.
</p><p>See also lists of
  <a href="objlist.html">Objects</a>,
  <a href="constlist.html">Constants</a>, and
  <a href="funclist.html">Functions</a>.</p>
